package org.sysdance.aplicativo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;

import org.sysdance.aplicativo.entidade.Turma;
import org.sysdance.aplicativo.excecao.ConexaoException;
import org.sysdance.aplicativo.excecao.DAOException;
import org.sysdance.aplicativo.integracao.TurmaIntegracao;
import org.sysdance.aplicativo.util.ConexaoUtil;
import org.sysdance.aplicativo.util.ResultSetUtil;
import org.sysdance.aplicativo.util.SysDanceUtil;

public class TurmaDAO implements TurmaIntegracao {

	@Override
	public Turma incluirTurma(Turma turma) throws DAOException {
		final StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO turma(id_turma, id_curso, nm_turma, qtd_max_alunos, turno, horario_inicio_turma,horario_fim_turma) ");
        sql.append("VALUES (NEXTVAL('turma_id_turma_seq'), ?, ?, ?, ?, ?, ?);");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString(),new String[]{"id_turma"});
            ps.setInt(1, turma.getCurso().getIdCurso());
            ps.setString(2, turma.getNmTurma());
            ps.setInt(3, turma.getQtdMaxAlunos());
            ps.setInt(4, turma.getTurno().getValor());
            ps.setString(5,turma.getHorarioInicioTurma());
            ps.setString(6, turma.getHorarioFimTurma());
            ps.executeUpdate();
            final ResultSet rs = ps.getGeneratedKeys();
        	if(rs.next()){
                turma.setIdTurma(rs.getInt(rs.getMetaData().getColumnName(1)));
            }
        } catch (final ConexaoException ex) {
            throw new DAOException(ex.getMessage());
        } catch (final SQLException sqlex) {
            throw new DAOException(sqlex.getMessage());
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return turma;
	}
	
	@Override
	public Collection<Turma> consultarTodasTurmas() {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM turma;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            return ResultSetUtil.extrair(ps.executeQuery(), Turma.class);
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return new LinkedList<Turma>();
	}
	
	@Override
	public Collection<Turma> consultarTurmasPorIdCurso(Integer idCurso) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM turma WHERE id_curso=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, idCurso);
            return ResultSetUtil.extrair(ps.executeQuery(), Turma.class);
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return new LinkedList<Turma>();
	}
	
	@Override
	public Boolean isExisteTurma(Turma turma) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT true FROM turma ");
        sql.append("WHERE horario_inicio_turma=? AND horario_fim_turma=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setString(1, turma.getHorarioInicioTurma());
            ps.setString(2, turma.getHorarioFimTurma());
            return ps.executeQuery().next();
        } catch (final ConexaoException ex) {
            return false;
        } catch (final SQLException sqlex) {
        	return false;
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
	}

}
